
using Youz.Common.DBUtility;
using Youz.Common.Entity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace Youz.Common.DAL
{
    /// <summary>
    /// Lang_Language Data Class
    /// </summary>
    public partial class Lang_LanguageDAL : BaseDAL
    {
        private DbHelperSQL DbHelperSQL = new DbHelperSQL(!string.IsNullOrEmpty(PubConstant.connStr_Lang) ? PubConstant.connStr_Lang : PubConstant.connStr);
        
        public string TableName = "Lang_Language";

        public Lang_LanguageDAL()
        {}

        #region  成员方法
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string ModuleName, string LanguageKey, SiteType WebSite)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Lang_Language");
            strSql.Append(" WHERE ModuleName = @ModuleName AND LanguageKey = @LanguageKey AND WebSite=@WebSite");
            SqlParameter[] parameters = {
					new SqlParameter("@ModuleName", ModuleName),
					new SqlParameter("@LanguageKey",  LanguageKey),
                    new SqlParameter("@WebSite", (int)WebSite)};

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        public bool Exists(Lang_Language model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Lang_Language");
            strSql.Append(" WHERE ModuleName = @ModuleName AND LanguageKey = @LanguageKey AND LanguageCode=@LanguageCode AND WebSite=@WebSite");
            SqlParameter[] parameters = {
					new SqlParameter("@ModuleName", model.ModuleName),
					new SqlParameter("@LanguageKey",  model.LanguageKey),
                    new SqlParameter("@LanguageCode",  model.LanguageCode),
                    new SqlParameter("@WebSite", model.WebSite)};

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        ///  新增一条数据
        /// </summary>
        public bool Add(Lang_Language model)
        {
            StringBuilder strSql = new StringBuilder();
			
            strSql.Append("insert into [Lang_Language](");
            strSql.Append("[LanguageCode],[ModuleName],[LanguageKey],[LanguageValue],[WebSite],[CreateTime],[ModifiedTime]");
            strSql.Append(") values (");
            strSql.Append("@LanguageCode,@ModuleName,@LanguageKey,@LanguageValue,@WebSite,getdate(),getdate()");
            strSql.Append(")");
			
            SqlParameter[] parameters = {
                  
                    new SqlParameter("@LanguageCode", model.LanguageCode),
                    new SqlParameter("@ModuleName", model.ModuleName),
                    new SqlParameter("@LanguageKey", model.LanguageKey),
                    new SqlParameter("@LanguageValue", model.LanguageValue),
                    new SqlParameter("@WebSite", model.WebSite)
            };

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Lang_Language model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Lang_Language set ");
            strSql.Append("LanguageValue=@LanguageValue,ModifiedTime=getdate()");
            strSql.Append(" where ModuleName=@ModuleName AND LanguageKey=@LanguageKey AND LanguageCode = @LanguageCode AND WebSite=@WebSite");
            SqlParameter[] parameters = {
					new SqlParameter("@ModuleName", model.ModuleName),
					new SqlParameter("@LanguageKey",  model.LanguageKey),
                    new SqlParameter("@LanguageValue", model.LanguageValue),
					new SqlParameter("@LanguageCode", model.LanguageCode),
                    new SqlParameter("@WebSite", model.WebSite)};

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0 ? true : false;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string ModuleName, string LanguageKey)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("DELETE FROM Lang_Language WHERE ModuleName = @ModuleName AND LanguageKey = @LanguageKey");
            SqlParameter[] parameters = {
					new SqlParameter("@ModuleName", ModuleName),
                    new SqlParameter("@LanguageKey", LanguageKey)};

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0 ? true : false;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Lang_Language Get(String ModuleName)
        {
            StringBuilder strSql = new StringBuilder();
			
            strSql.Append("select  top 1 * from [Lang_Language] ");
            strSql.Append(" where [ModuleName]=@ModuleName");
			
            SqlParameter[] parameters = {
			
                new SqlParameter("ModuleName", ModuleName)             
              
            };

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return Get(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

		/// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Lang_Language Get(DataRow row)
        {
            if (row == null) return null;

            Lang_Language model = new Lang_Language();
	
            
            if (row["LanguageCode"] != DBNull.Value)
            {
                model.LanguageCode = (String)row["LanguageCode"];
            }      
            
            if (row["ModuleName"] != DBNull.Value)
            {
                model.ModuleName = (String)row["ModuleName"];
            }      
            
            if (row["LanguageKey"] != DBNull.Value)
            {
                model.LanguageKey = (String)row["LanguageKey"];
            }      
            
            if (row["LanguageValue"] != DBNull.Value)
            {
                model.LanguageValue = (String)row["LanguageValue"];
            }      
            
            if (row["WebSite"] != DBNull.Value)
            {
                model.WebSite = (Int32)row["WebSite"];
            }      
            
            if (row["CreateTime"] != DBNull.Value)
            {
                model.CreateTime = (DateTime)row["CreateTime"];
            }      
            
            if (row["ModifiedTime"] != DBNull.Value)
            {
                model.ModifiedTime = (DateTime)row["ModifiedTime"];
            }      
            

            return model;
        }

        /// <summary>
        /// 獲取所有数据
        /// </summary>
        /// <param name="site"></param>
        /// <returns></returns>
        public DataTable Gets(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from [dbo].[Lang_Language] ");
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.AppendFormat(" where {0}", strWhere);
            }

            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds != null && ds.Tables.Count >= 0)
            {
                return ds.Tables[0];
            }
            else
            {
                return new DataTable();
            }
        }

        /// <summary>
        /// 获取语言模块
        /// </summary>
        /// <param name="SiteType"></param>
        /// <returns></returns>
        public List<string> GetModules(SiteType SiteType)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ModuleName from [dbo].[Lang_Language] where WebSite=@SiteType group by ModuleName order by ModuleName");

            SqlParameter[] paras = { new SqlParameter("@SiteType", (int)SiteType) };

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), paras);
            if (ds != null && ds.Tables.Count >= 0)
            {
                List<string> list = new List<string>();
                DataTable dt = ds.Tables[0];
                foreach (DataRow row in dt.Rows)
                {
                    list.Add(row[0].ToString());
                }

                return list;
            }
            else
            {
                return new List<string>();
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataTable GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"declare @sql nvarchar(max)
                        select @sql=isnull(@sql+',','') + [LanguageCode] from [Lang_LanguageType] group by [LanguageCode] order by [LanguageCode]
                        set @sql='select * from (select [ModuleName],[LanguageKey],LanguageValue,LanguageCode,WebSite from [Lang_Language]) as Lang 
                                  pivot (max([LanguageValue]) for [LanguageCode] in ('+@sql+')) a ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere.Replace("'", "''"));
            }
            strSql.Append(@"order by [ModuleName],[LanguageKey]' 
                        exec(@sql)");

            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds != null && ds.Tables.Count >= 0)
            {
                return ds.Tables[0];
            }
            else
            {
                return new DataTable();
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataTable GetList(string strWhere, int pageIndex, int pageSize, out int total)
        {
            total = 0;
            int skip = pageIndex + 1;
            int take = pageSize + pageIndex;

            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"declare @sql nvarchar(max)
                        select @sql=isnull(@sql+',','') + [LanguageCode] from [Lang_LanguageType] group by [LanguageCode] order by [LanguageCode]
                        set @sql='");

            string sql = ("select * from (select [ModuleName],[LanguageKey],LanguageValue,LanguageCode,WebSite from [Lang_Language]) as Lang pivot (max([LanguageValue]) for [LanguageCode] in ('+@sql+')) a ");

            if (strWhere.Trim() != "")
            {
                sql += (" where " + strWhere.Replace("'", "''"));
            }

            strSql.AppendFormat(@"select count(*) from
                        (
	                        {0}
                        ) TableCount ", sql);

            strSql.AppendFormat(@"select * from
                                        (
                                               select *,ROW_NUMBER() OVER ({0}) as RowIndex from
	                                           (
			                                        {1}
	                                           ) table1
                                        )  tableRecord where tableRecord.RowIndex between {2} and {3}", "order by [ModuleName],[LanguageKey]", sql, skip, take);
            strSql.Append("'");
            strSql.Append(@"exec(@sql)");

            DataSet ds = DbHelperSQL.Query(strSql.ToString());

            if (ds == null || ds.Tables.Count == 0)
            {
                total = 0;
                return null;
            }
            else
            {
                total = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                DataTable dt = ds.Tables[1];
                return dt;
            }

        }
      #endregion  成员方法
    }
}


  